/*
 * Copyright (C) 2009 2DAI
 *
 * Este programa es Software Libre; usted puede redistribuirlo
 * y/o modificarlo bajo los términos de la "GNU General Public
 * License" como lo publica la "FSF Free Software Foundation",
 * o (a su elección) de cualquier versión posterior.
 *
 * Este programa es distribuido con la esperanza de que le será
 * útil, pero SIN NINGUNA GARANTIA; incluso sin la garantía
 * implícita por el MERCADEO o EJERCICIO DE ALGUN PROPOSITO en
 * particular. Vea la "GNU General Public License" para más
 * detalles.
 *
 * Usted debe haber recibido una copia de la "GNU General Public
 * License" junto con este programa, si no, escriba a la "FSF
 * Free Software Foundation, Inc.", 59 Temple Place - Suite 330,
 * Boston, MA  02111-1307, USA.
 */
package data;
import util.Errors;
import com.mysql.jdbc.ResultSet;
import java.sql.SQLException;
import java.util.logging.Level;
import java.util.logging.Logger;
import on.Referees;


/**
 * 
 * Author: Marta Espejo
 */
public class DataReferee extends DataManager<Referees> {

    private String tabla = "referee";

    public DataReferee() {
        super();
        setTabla(tabla);
        requerir();
    }

    @Override
    public void requerir() {
        try {
            sql.conectar();
            ResultSet rs = (ResultSet) sql.ejecutarSelect("select num_licence, " +
                    "last_name,first_name,dni,postal_code," +
                    "address,city,province,league,phone,email,birth_date,nationality " +
                    "from " + tabla);
            while (rs.next()) {
                Referees referee = new Referees();
                referee.setNumLicencia(rs.getInt(1));
                referee.setApellidos(rs.getString(2));
                referee.setNombre(rs.getString(3));
                referee.setDni(rs.getInt(4));
                referee.setCodigoPostal(rs.getInt(5));
                referee.setDireccion(rs.getString(6));
                referee.setPoblacion(rs.getString(7));
                referee.setProvincia(rs.getString(8));
                referee.setLiga(rs.getString(9));
                referee.setTelefono(rs.getInt(10));
                referee.setEMail(rs.getString(11));
                referee.setFechaNacimiento(rs.getDate(12));
                referee.setNacionalidad(rs.getString(13));
                getList().add(referee);

            }
        } catch (SQLException ex) {
            Logger.getLogger(DataReferee.class.getName()).log(Level.SEVERE, null, ex);
        }
        sql.desconectar();
    }

    @Override
    public void guardar(Referees o) {
        sql.conectar();
        getList().set(index, o);
        sql.ejectarUpdate("UPDATE "  + tabla +
                " SET num_licence=" + o.getNumLicencia() + "," +
                "last_name='" + o.getApellidos() + "'," +
                "first_name='" + o.getNombre() + "'," +
                "dni=" + o.getDni() + "," +
                "postal_code=" + o.getCodigoPostal() + "," +
                "address='" + o.getDireccion() + "'," +
                "city='" + o.getPoblacion() + "'," +
                "province='" + o.getProvincia() + "'," +
                "league='" + o.getLiga() + "'," +
                "phone=" + o.getTelefono() + "," +
                "email='" + o.getEMail() + "'," +
               // "birth_date='" + o.getFechaNacimiento() + "'," +
                "nationality='" + o.getNacionalidad() +"'"+
                " WHERE id_referee= " + o.getId_Arbitros());
        sql.desconectar();
    }

    @Override
    public void insertar(Referees o) {
        sql.conectar();
        getList().add(o);        
        String d = String.format("%1$tY-%1$tm-%1$t" + "e", o.getFechaNacimiento());
        sql.ejectarUpdate("INSERT INTO " + tabla +
                " (id_referee,num_licence,last_name,first_name,dni,postal_code,address,city,province,league,phone,email,birth_date,nationality) " +
                " VALUES("+o.getId_Arbitros()+"," + o.getNumLicencia() + ",'" + o.getApellidos() + "','" + o.getNombre() + "'," +
                o.getDni() + "," + o.getCodigoPostal()+ ",'" + o.getDireccion() + "','" + o.getPoblacion() + "','" +
                o.getProvincia() + "','" + o.getLiga() + "'," + o.getTelefono() + ",'" + o.getEMail() + "','" +d+ "','" + o.getNacionalidad()+ "')");
        sql.desconectar();

        
    }

    @Override
    public void eliminar(Referees o) {
        sql.conectar();
        getList().remove(index);
        sql.ejectarUpdate("DELETE FROM" + tabla + "WHERE id_referee=" + o.getId_Arbitros());
        sql.desconectar();
    }

    /**
     * mÃ©todo que pasandole el id del arbireo nos devuelve toda su informaciÃ³n
     * @param id
     * @return referee
     */
    public Referees obtenerArbitro(int id) {
        Referees referee = new Referees();
        try {
            sql.conectar();
            ResultSet rs = (ResultSet) sql.ejecutarSelect("SELECT * FROM " + tabla +
                    " WHERE id_referee= " + id);
            rs.next();

            referee.setNumLicencia(rs.getInt(2));
            referee.setApellidos(rs.getString(3));
            referee.setNombre(rs.getString(4));
            referee.setDni(rs.getInt(5));
            referee.setCodigoPostal(rs.getInt(6));
            referee.setDireccion(rs.getString(7));
            referee.setPoblacion(rs.getString(8));
            referee.setProvincia(rs.getString(9));
            referee.setLiga(rs.getString(10));
            referee.setTelefono(rs.getInt(11));
            referee.setEMail(rs.getString(12));
            referee.setFechaNacimiento(rs.getDate(13));
            referee.setNacionalidad(rs.getString(14));

        } catch (SQLException ex) {
            Errors.creaError("ERROR: No se han podido conseguir los datos\n" + ex.getMessage(), ex.getStackTrace());
        } finally {
            sql.desconectar();
            return referee;
        }
    }

    @Override
    public void crear(String tabla) {
        sql.conectar();
        sql.ejectarUpdate("DROP TABLE IF EXISTS" + tabla + ";" +
                "CREATE TABLE IF NOT EXISTS" + tabla + "(" +
                "id_referee int(11) NOT NULL auto_increment," +
                "num_licence  int(11) NOT NULL," +
                "last_name  varchar(20) NOT NULL," +
                "first_name  varchar(20) NOT NULL," +
                "dni  int(8) NOT NULL," +
                "postal_code  int(6) NOT NULL," +
                "address  varchar(30) NOT NULL," +
                "city  varchar(30) NOT NULL," +
                "province  varchar(30) NOT NULL," +
                "league  varchar(3) NOT NULL," +
                "phone  int(11) default NULL," +
                "email  varchar(40) default NULL," +
                "birth_date date NOT NULL," +
                "nacionality varchar(3) NOT NULL," +
                "PRIMARY KEY  ( id_referee )" +
                ")ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;");
    }
}
